﻿CREATE PROCEDURE [RestartFramework].[PutETLJob]
		@pETLJobName	SYSNAME
,		@pETLJobID		SMALLINT	=   NULL		OUTPUT
,       @pPrint         BIT         =   1
AS
BEGIN
		SET NOCOUNT ON;
		BEGIN TRY
				DECLARE  @vRowCount  INT = 0;
				INSERT	[RestartFramework].[ETLJob]([ETLJobId],[ETLJobName])
				SELECT	[ETLJobId]		=	NEXT VALUE FOR [RestartFramework].[ETLJobId]
				,		[ETLJobName]	=	src.[ETLJobName]
				FROM	(
						SELECT	[ETLJobName]	=	@pETLJobName
						) src
				LEFT OUTER JOIN [RestartFramework].[ETLJob] tgt		ON	src.[ETLJobName]	=	tgt.[ETLJobName]
				WHERE	tgt.[ETLJobName] IS NULL;
				SET @vRowCount = @@ROWCOUNT;

				SET	@pETLJobID	=	(
									SELECT	ej.[ETLJobId]
									FROM	[RestartFramework].[ETLJob] ej
									WHERE	ej.[ETLJobName] = @pETLJobName
									);
				IF(@pPrint = 1)
						PRINT   CONVERT(NVARCHAR(5),@vRowCount) + N' rows inserted into [RestartFramework].[ETLJob] for ETLJobName=' + 
										@pETLJobName + N', ETLJobID=' + CONVERT(NVARCHAR(10),@pETLJobID);
		END TRY
		BEGIN CATCH
				SET	@pETLJobID = NULL;
				THROW;
		END CATCH
END